﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using points.Db;
using points.Interface;
using System.Configuration;
using points.AppInfo;

namespace points.Model
{
    public class Customer : DbAssist, IStorable
    {
        public int Pk { get; set; }
        public string Obj_Name { get; set; }
        public DateTime Add_Dt { get; set; }
        public DateTime Mod_Dt { get; set; }
        public string Marked_Delete_Y_N { get; set; }
        public int Rec_Id { get; set; }
        public int Fk_Locale { get; set; }
        public int Fk_Customer_Type_Id { get; set; }
        public int Fk_Customer_Status_Type_Id { get; set; }
        public string Userid { get; set; }
        public string Password { get; set; }
        public string Salutation { get; set; }
        public string Name_First { get; set; }
        public string Name_First_Canon { get; set; }
        public string Name_Middle { get; set; }
        public string Name_Middle_Canon { get; set; }
        public string Name_Last { get; set; }
        public string Name_Last_Canon { get; set; }
        public string Email { get; set; }
        public string Web { get; set; }
        public string Descr { get; set; }

        public int ValPoint { get; set; }
        public int ValDollar { get; set; }

        public Customer() { }

        public Customer
        (
            int pk,
            DateTime addDt,
            DateTime modDt,
            string markedDeleteYN,
            int recId,
            int fkLocale,
            int fkCustomerTypeId,
            int fkCustomerStatusTypeId,
            string userId,
            string password,
            string salutation,
            string nameFirst,
            string nameMiddle,
            string nameLast,
            string email,
            string website,
            string descr,
            int valPoint,
            int valDollar
        ) 
        { 
            Init
            (
                pk,
                addDt,
                modDt,
                markedDeleteYN,
                recId,
                fkLocale,
                fkCustomerTypeId,
                fkCustomerStatusTypeId,
                userId,
                password,
                salutation,
                nameFirst,
                nameMiddle,
                nameLast,
                email,
                website,
                descr,
                valPoint,
                valDollar
            ); 
        }

        public void Init
        (
            int pk,
            DateTime addDt,
            DateTime modDt,
            string markedDeleteYN,
            int recId,
            int fkLocale,
            int fkCustomerTypeId,
            int fkCustomerStatusTypeId,
            string userId,
            string password,
            string salutation,
            string nameFirst,
            string nameMiddle,
            string nameLast,
            string email,
            string website,
            string descr,
            int valPoint,
            int valDollar
        )
        {
            Pk = pk;
            Add_Dt = addDt;
            Mod_Dt = modDt;
            Obj_Name = ConfigurationManager.AppSettings["OBJECT_CUSTOMER"];
            Marked_Delete_Y_N = markedDeleteYN;
            Rec_Id = recId;
            Fk_Locale = fkLocale;
            Fk_Customer_Type_Id = fkCustomerTypeId;
            Fk_Customer_Status_Type_Id = fkCustomerStatusTypeId;
            Userid = userId;
            Password = password;
            Salutation = salutation;
            Name_First = nameFirst;
            Name_First_Canon = AppVars.StringNotNull(Name_First).ToLower();
            Name_Middle = nameMiddle;
            Name_Middle_Canon = AppVars.StringNotNull(Name_Middle).ToLower();
            Name_Last = nameLast;
            Name_Last_Canon = AppVars.StringNotNull(Name_Last).ToLower();
            Email = email;
            Web = website;
            Descr = descr;
            ValPoint = valPoint;
            ValDollar = valDollar;
        }



        public int Delete()
        {
            int retVal = 0;

            return retVal;
        }

        public int Update()
        {
            int retVal = 0;

            return retVal;
        }


 
        
        /*
        public Customer Select(string userid, string password)
        {
            //ADOConnection conn = new ADOConnection(DB_CONN_STRING);
            //string strConn = ConfigurationManager.AppSettings["POINTS_PROD"];
            string strConn = (string)ConfigurationManager.ConnectionStrings["POINTS_PROD"].ToString();
            DataSet ds = null;

            int cstActivePk = (AppVars.customer_status_type["active"]).Pk;
            string strSelect =
            "SELECT" +
                " c.pk," +
                " c.obj_name," +
                " c.add_dt," +
                " c.mod_dt," +
                " c.marked_delete_y_n," +
                " c.rec_id," +
                " c.fk_customer_type_id," +
                " c.fk_customer_status_type_id," +
                " c.userid," +
                " c.password," +
                " c.salutation," +
                " c.name_first," +
                " c.name_first_canon," +
                " c.name_middle," +
                " c.name_middle_canon," +
                " c.name_last," +
                " c.name_last_canon," +
                " c.email," +
                " c.web," +
                " c.descr" +
            " from customer c" +
            " where c.fk_customer_status_type_id= " + cstActivePk.ToString() +
            " and c.userid=@paramUserId" +
            " and c.password=@paramPassword";

            using (SqlConnection conn = new SqlConnection(strConn))
            {
                try
                {
                    using (SqlCommand cmd = new SqlCommand(strSelect))
                    {                        
                        ds = new DataSet();

                        SqlParameter paramUserId = new SqlParameter("@paramUserId", SqlDbType.NVarChar);
                        SqlParameter paramPassword = new SqlParameter("@paramPassword", SqlDbType.NVarChar);

                        cmd.Parameters.Add(paramUserId);
                        cmd.Parameters.Add(paramPassword);

                        paramUserId.Value = userid;
                        paramPassword.Value = password;

                        //conn.Open();
                        //cmdSelect.ExecuteReader();
                        using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd))
                        {
                            sqlDataAdapter.Fill(ds);
                        }

                        //using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmdSelect, conn))
                        //{
                            //sqlDataAdapter.Fill(ds);
                        //}
                    }
                }
                finally
                {
                    try
                    {
                        conn.Close();
                    }
                    catch { }
                }
            }

            if (ds != null)
            {
                DataTable dt = ds.Tables[0];
                if (dt != null)
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        int i = -1;

                        Pk = (Int32)row[++i];
                        Obj_Name = (string)row[++i];
                        Add_Dt = (DateTime)row[++i];
                        Mod_Dt = (DateTime)row[++i];
                        Marked_Delete_Y_N = (string)row[++i];
                        Rec_Id = (Int32)row[++i];
                        Fk_Customer_Type_Id = (Int32)row[++i];
                        Fk_Customer_Status_Type_Id = (Int32)row[++i];
                        Userid = (string)row[++i];
                        Password = (string)row[++i];
                        Salutation = (string)row[++i];
                        Name_First = (string)row[++i];
                        Name_First_Canon = (string)row[++i];
                        Name_Middle = (string)row[++i];
                        Name_Middle_Canon = (string)row[++i];
                        Name_Last = (string)row[++i];
                        Name_Last_Canon = (string)row[++i];
                        Email = (string)row[++i];
                        Web = (string)row[++i];
                        Descr = (string)row[++i];
                    }
                }
            }

            return this;
        }
        */


        public Customer Select(string userid, string password)
        {
            string strConn = (string)ConfigurationManager.ConnectionStrings["POINTS_PROD"].ToString();
            DataSet ds = null;

            using (SqlConnection conn = new SqlConnection(strConn))
            {
                try
                {
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        int cstActivePk = (AppVars.customer_status_type["active"]).Pk;
                        ds = new DataSet();

                        string cmdSelect =
                        "SELECT" +
                            " c.pk," +
                            " c.obj_name," +
                            " c.add_dt," +
                            " c.mod_dt," +
                            " c.marked_delete_y_n," +
                            " c.rec_id," +
                            " c.fk_customer_type_id," +
                            " c.fk_customer_status_type_id," +
                            " c.userid," +
                            " c.password," +
                            " c.salutation," +
                            " c.name_first," +
                            " c.name_first_canon," +
                            " c.name_middle," +
                            " c.name_middle_canon," +
                            " c.name_last," +
                            " c.name_last_canon," +
                            " c.email," +
                            " c.web," +
                            " c.descr," +
                            " c.val_point," +
                            " c.val_dollar" +
                        " from customer c" +
                        " where c.fk_customer_status_type_id= " + cstActivePk.ToString() +
                        " and c.userid = @paramUserId" +
                        " and c.password = @paramPassword";

                        using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmdSelect, conn))
                        {
                            SqlParameter paramUserId = new SqlParameter("@paramUserId", SqlDbType.NVarChar);
                            SqlParameter paramPassword = new SqlParameter("@paramPassword", SqlDbType.NVarChar);

                            paramUserId.Value = userid;
                            paramPassword.Value = password;

                            //cmd.Parameters.Add(paramUserId);
                            //cmd.Parameters.Add(paramPassword);

                            sqlDataAdapter.SelectCommand.Parameters.Add(paramUserId);
                            sqlDataAdapter.SelectCommand.Parameters.Add(paramPassword);

                            sqlDataAdapter.Fill(ds);
                        }
                    }
                }
                finally
                {
                    try
                    {
                        conn.Close();
                    }
                    catch { }
                }
            }

            if (ds != null)
            {
                DataTable dt = ds.Tables[0];
                if (dt != null)
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        int i = -1;

                        Pk = (Int32)row[++i];
                        Obj_Name = (string)row[++i];
                        Add_Dt = (DateTime)row[++i];
                        Mod_Dt = (DateTime)row[++i];
                        Marked_Delete_Y_N = (string)row[++i];
                        Rec_Id = (Int32)row[++i];
                        Fk_Customer_Type_Id = (Int32)row[++i];
                        Fk_Customer_Status_Type_Id = (Int32)row[++i];
                        Userid = (string)row[++i];
                        Password = (string)row[++i];
                        Salutation = (string)row[++i];
                        Name_First = (string)row[++i];
                        Name_First_Canon = (string)row[++i];
                        Name_Middle = (string)row[++i];
                        Name_Middle_Canon = (string)row[++i];
                        Name_Last = (string)row[++i];
                        Name_Last_Canon = (string)row[++i];
                        Email = (string)row[++i];
                        Web = (string)row[++i];
                        Descr = (string)row[++i];
                        ValPoint = (Int32)row[++i];
                        ValDollar = (Int32)row[++i];
                    }
                }
            }

            return this;
        }



        public Customer Select(string userid)
        {
            string strConn = (string)ConfigurationManager.ConnectionStrings["POINTS_PROD"].ToString();
            DataSet ds = null;

            using (SqlConnection conn = new SqlConnection(strConn))
            {
                try
                {
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        int cstActivePk = (AppVars.customer_status_type["active"]).Pk;
                        ds = new DataSet();

                        string cmdSelect =
                        "SELECT" +
                            " c.pk," +
                            " c.obj_name," +
                            " c.add_dt," +
                            " c.mod_dt," +
                            " c.marked_delete_y_n," +
                            " c.rec_id," +
                            " c.fk_customer_type_id," +
                            " c.fk_customer_status_type_id," +
                            " c.userid," +
                            " c.password," +
                            " c.salutation," +
                            " c.name_first," +
                            " c.name_first_canon," +
                            " c.name_middle," +
                            " c.name_middle_canon," +
                            " c.name_last," +
                            " c.name_last_canon," +
                            " c.email," +
                            " c.web," +
                            " c.descr," +
                            " c.val_point," +
                            " c.val_dollar" +
                        " from customer c" +
                        " where c.fk_customer_status_type_id= " + cstActivePk.ToString() +
                        " and c.userid = @paramUserId";

                        using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmdSelect, conn))
                        {
                            SqlParameter paramUserId = new SqlParameter("@paramUserId", SqlDbType.NVarChar);
                            //SqlParameter paramPassword = new SqlParameter("@paramPassword", SqlDbType.NVarChar);

                            paramUserId.Value = userid;
                            //paramPassword.Value = password;

                            //cmd.Parameters.Add(paramUserId);
                            //cmd.Parameters.Add(paramPassword);

                            sqlDataAdapter.SelectCommand.Parameters.Add(paramUserId);
                            //sqlDataAdapter.SelectCommand.Parameters.Add(paramPassword);

                            sqlDataAdapter.Fill(ds);
                        }
                    }
                }
                finally
                {
                    try
                    {
                        conn.Close();
                    }
                    catch { }
                }
            }

            if (ds != null)
            {
                DataTable dt = ds.Tables[0];
                if (dt != null)
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        int i = -1;

                        Pk = (Int32)row[++i];
                        Obj_Name = (string)row[++i];
                        Add_Dt = (DateTime)row[++i];
                        Mod_Dt = (DateTime)row[++i];
                        Marked_Delete_Y_N = (string)row[++i];
                        Rec_Id = (Int32)row[++i];
                        Fk_Customer_Type_Id = (Int32)row[++i];
                        Fk_Customer_Status_Type_Id = (Int32)row[++i];
                        Userid = (string)row[++i];
                        Password = (string)row[++i];
                        Salutation = (string)row[++i];
                        Name_First = (string)row[++i];
                        Name_First_Canon = (string)row[++i];
                        Name_Middle = (string)row[++i];
                        Name_Middle_Canon = (string)row[++i];
                        Name_Last = (string)row[++i];
                        Name_Last_Canon = (string)row[++i];
                        Email = (string)row[++i];
                        Web = (string)row[++i];
                        Descr = (string)row[++i];
                        ValPoint = (Int32)row[++i];
                        ValDollar = (Int32)row[++i];
                    }
                }
            }

            return this;
        }

        public Customer Select(int pk)
        {
            string strConn = (string)ConfigurationManager.ConnectionStrings["POINTS_PROD"].ToString();
            DataSet ds = null;

            using (SqlConnection conn = new SqlConnection(strConn))
            {
                try
                {
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        int cstActivePk = (AppVars.customer_status_type["active"]).Pk;
                        ds = new DataSet();

                        string cmdSelect =
                        "SELECT" +
                            " c.pk," +
                            " c.obj_name," +
                            " c.add_dt," +
                            " c.mod_dt," +
                            " c.marked_delete_y_n," +
                            " c.rec_id," +
                            " c.fk_customer_type_id," +
                            " c.fk_customer_status_type_id," +
                            " c.userid," +
                            " c.password," +
                            " c.salutation," +
                            " c.name_first," +
                            " c.name_first_canon," +
                            " c.name_middle," +
                            " c.name_middle_canon," +
                            " c.name_last," +
                            " c.name_last_canon," +
                            " c.email," +
                            " c.web," +
                            " c.descr," +
                            " c.val_point," +
                            " c.val_dollar" +
                        " from customer c" +
                        " where c.fk_customer_status_type_id= " + cstActivePk.ToString() +
                        " and c.pk = @paramPk";

                        using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmdSelect, conn))
                        {
                            SqlParameter paramPk = new SqlParameter("@paramPk", SqlDbType.Int);
                            //SqlParameter paramPassword = new SqlParameter("@paramPassword", SqlDbType.NVarChar);

                            paramPk.Value = pk;
                            //paramPassword.Value = password;

                            //cmd.Parameters.Add(paramUserId);
                            //cmd.Parameters.Add(paramPassword);

                            sqlDataAdapter.SelectCommand.Parameters.Add(paramPk);
                            //sqlDataAdapter.SelectCommand.Parameters.Add(paramPassword);

                            sqlDataAdapter.Fill(ds);
                        }
                    }
                }
                finally
                {
                    try
                    {
                        conn.Close();
                    }
                    catch { }
                }
            }

            if (ds != null)
            {
                DataTable dt = ds.Tables[0];
                if (dt != null)
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        int i = -1;

                        Pk = (Int32)row[++i];
                        Obj_Name = (string)row[++i];
                        Add_Dt = (DateTime)row[++i];
                        Mod_Dt = (DateTime)row[++i];
                        Marked_Delete_Y_N = (string)row[++i];
                        Rec_Id = (Int32)row[++i];
                        Fk_Customer_Type_Id = (Int32)row[++i];
                        Fk_Customer_Status_Type_Id = (Int32)row[++i];
                        Userid = (string)row[++i];
                        Password = (string)row[++i];
                        Salutation = (string)row[++i];
                        Name_First = (string)row[++i];
                        Name_First_Canon = (string)row[++i];
                        Name_Middle = (string)row[++i];
                        Name_Middle_Canon = (string)row[++i];
                        Name_Last = (string)row[++i];
                        Name_Last_Canon = (string)row[++i];
                        Email = (string)row[++i];
                        Web = (string)row[++i];
                        Descr = (string)row[++i];
                        ValPoint = (Int32)row[++i];
                        ValDollar = (Int32)row[++i];
                    }
                }
            }

            return this;
        }

        /*
        public Customer Select(string userid, string password)
        {
            int cstActivePk = (AppVars.customer_status_type["active"]).Pk;

            string cmdSelect =
                        "SELECT" +
                            " c.pk," +
                            " c.obj_name," +
                            " c.add_dt," +
                            " c.mod_dt," +
                            " c.marked_delete_y_n," +
                            " c.rec_id," +
                            " c.fk_customer_type_id," +
                            " c.fk_customer_status_type_id," +
                            " c.userid," +
                            " c.password," +
                            " c.salutation," +
                            " c.name_first," +
                            " c.name_first_canon," +
                            " c.name_middle," +
                            " c.name_middle_canon," +
                            " c.name_last," +
                            " c.name_last_canon," +
                            " c.email," +
                            " c.web," +
                            " c.descr" +
                        " from customer c" +
                        " where c.fk_customer_status_type_id= " + cstActivePk.ToString() +
                        " and c.userid = @paramUserId" +
                        " and c.password = @paramPassword";

            using (SqlConnection connection = new SqlConnection(cmdSelect))
            {
                DataSet userDataset = new DataSet();
                SqlDataAdapter myCommand = new SqlDataAdapter(
                           "LoginStoredProcedure", connection);
                myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
                myCommand.SelectCommand.Parameters.Add("@au_id", SqlDbType.VarChar, 11);
                myCommand.SelectCommand.Parameters["@au_id"].Value = SSN.Text;

                myCommand.Fill(userDataset);
            }
        }
        */





        /*
        public Customer Select(string userid, string password)
        {
            string strConn = (string)ConfigurationManager.ConnectionStrings["POINTS_PROD"].ToString();
            int cstActivePk = (AppVars.customer_status_type["active"]).Pk;

            string cmdSelect =
            "SELECT" +
                " c.pk," +
                " c.obj_name," +
                " c.add_dt," +
                " c.mod_dt," +
                " c.marked_delete_y_n," +
                " c.rec_id," +
                " c.fk_customer_type_id," +
                " c.fk_customer_status_type_id," +
                " c.userid," +
                " c.password," +
                " c.salutation," +
                " c.name_first," +
                " c.name_first_canon," +
                " c.name_middle," +
                " c.name_middle_canon," +
                " c.name_last," +
                " c.name_last_canon," +
                " c.email," +
                " c.web," +
                " c.descr" +
            " from customer c" +
            " where c.fk_customer_status_type_id= " + cstActivePk.ToString() +
            " and c.userid = @paramUserId" +
            " and c.password = @paramPassword";

            using (SqlConnection conn = new SqlConnection(strConn))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {

                    using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmdSelect, conn))
                    {
                    

                        DataSet userDataset = new DataSet();
                        //SqlDataAdapter myDataAdapter = new SqlDataAdapter(cmdSelect,connection);

                        SqlParameter paramUserId = new SqlParameter("@paramUserId", SqlDbType.NVarChar);
                        SqlParameter paramPassword = new SqlParameter("@paramPassword", SqlDbType.NVarChar);

                        paramUserId.Value = userid;
                        paramPassword.Value = password;

                        //cmd.Parameters.Add(paramUserId);
                        //cmd.Parameters.Add(paramPassword);

                        sqlDataAdapter.SelectCommand.Parameters.Add(paramUserId);
                        sqlDataAdapter.SelectCommand.Parameters.Add(paramPassword);
                        //myCommand.SelectCommand.Parameters.Add("@au_id", SqlDbType.VarChar, 11);
                        //myCommand.SelectCommand.Parameters["@au_id"].Value = SSN.Text;

                        sqlDataAdapter.Fill(userDataset);
                    }
                }
            }
        }
        */


        public int Insert2()
        {
            int rowsAffected = 0;

            //ADOConnection conn = new ADOConnection(DB_CONN_STRING);
            //string strConn = ConfigurationManager.AppSettings["POINTS_PROD"];
            string strConn = (string)ConfigurationManager.ConnectionStrings["POINTS_PROD"].ToString();

            using (SqlConnection conn = new SqlConnection(strConn))
            //using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings("POINTS_PROD")))
            {
                conn.Open();

                //SqlParameter paramPk = new SqlParameter("@param1", SqlDbType.NVarChar);
                SqlParameter paramAddDt = new SqlParameter("@paramAddDt", SqlDbType.DateTime2);
                SqlParameter paramModDt = new SqlParameter("@paramModDt", SqlDbType.DateTime2);
                SqlParameter paramObjName = new SqlParameter("@paramObjName", SqlDbType.NVarChar);
                SqlParameter paramMarkedDeleteYN = new SqlParameter("@paramMarkedDeleteYN", SqlDbType.NVarChar);
                SqlParameter paramRecId = new SqlParameter("@paramRecId", SqlDbType.Int);
                //SqlParameter paramFkCustomerTypeId = new SqlParameter("@paramFkCustomerTypeId", SqlDbType.Int);
                ///SqlParameter paramFkCustomerStatusTypeId = new SqlParameter("@paramFkCustomerStatusTypeId", SqlDbType.Int);
                SqlParameter paramUserId = new SqlParameter("@paramUserId", SqlDbType.NVarChar);
                SqlParameter paramPassword = new SqlParameter("@paramPassword", SqlDbType.NVarChar);
                SqlParameter paramSalutation = new SqlParameter("@paramSalutation", SqlDbType.NVarChar);
                SqlParameter paramNameFirst = new SqlParameter("@paramNameFirst", SqlDbType.NVarChar);
                SqlParameter paramNameFirstCanon = new SqlParameter("@paramNameFirstCanon", SqlDbType.NVarChar);
                SqlParameter paramNameMiddle = new SqlParameter("@paramNameMiddle", SqlDbType.NVarChar);
                SqlParameter paramNameMiddleCanon = new SqlParameter("@paramNameMiddleCanon", SqlDbType.NVarChar);
                SqlParameter paramNameLast = new SqlParameter("@paramNameLast", SqlDbType.NVarChar);
                SqlParameter paramNameLastCanon = new SqlParameter("@paramNameLastCanon", SqlDbType.NVarChar);
                SqlParameter paramEmail = new SqlParameter("@paramEmail", SqlDbType.NVarChar);
                SqlParameter paramWebsite = new SqlParameter("@paramWebsite", SqlDbType.NVarChar);
                SqlParameter paramDescr = new SqlParameter("@paramDescr", SqlDbType.NVarChar);


                paramAddDt.Value = this.Add_Dt;
                paramModDt.Value = this.Mod_Dt;
                paramObjName.Value = this.Obj_Name;
                paramMarkedDeleteYN.Value = this.Marked_Delete_Y_N;
                paramRecId.Value = this.Rec_Id;
                //paramFkCustomerTypeId.Value=this.Fk_Customer_Type_Id;
                //paramFkCustomerStatusTypeId.Value = this.Fk_Customer_Status_Type_Id;
                paramUserId.Value = this.Userid;
                paramPassword.Value = this.Password;
                paramSalutation.Value = this.Salutation;
                paramNameFirst.Value = this.Name_First;
                paramNameFirstCanon.Value = this.Name_First_Canon;
                paramNameMiddle.Value = this.Name_Middle;
                paramNameMiddleCanon.Value = this.Name_Middle_Canon;
                paramNameLast.Value = this.Name_Last;
                paramNameLastCanon.Value = this.Name_Last_Canon;
                paramEmail.Value = this.Email;
                paramWebsite.Value = this.Web;
                paramDescr.Value = this.Descr;

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    string sqlString =
                    "INSERT INTO CUSTOMER " +
                    "SELECT " +
                        "@paramObjName, " +
                        "@paramAddDt, " +
                        "@paramModDt, " +
                        "@paramMarkedDeleteYN, " +
                        "@paramRecId, " +
                        "ct.pk, " +
                        "cst.pk, " +
                        "@paramUserId, " +
                        "@paramPassword, " +
                        "@paramSalutation, " +
                        "@paramNameFirst, " +
                        "@paramNameFirstCanon, " +
                        "@paramNameMiddle, " +
                        "@paramNameMiddleCanon, " +
                        "@paramNameLast, " +
                        "@paramNameLastCanon, " +
                        "@paramEmail, " +
                        "@paramWebsite, " +
                        "@paramDescr " +
                    "from customer_type ct, customer_status_type cst " +
                    "where ct.name_canon='end_user' " +
                    "and cst.name_canon='active'";

                    cmd.CommandText = sqlString;

                    cmd.Parameters.Add(paramAddDt);
                    cmd.Parameters.Add(paramModDt);
                    cmd.Parameters.Add(paramObjName);
                    cmd.Parameters.Add(paramMarkedDeleteYN);
                    cmd.Parameters.Add(paramRecId);
                    //cmd.Parameters.Add(paramFkCustomerTypeId);
                    //cmd.Parameters.Add(paramFkCustomerStatusTypeId);
                    cmd.Parameters.Add(paramUserId);
                    cmd.Parameters.Add(paramPassword);
                    cmd.Parameters.Add(paramSalutation);
                    cmd.Parameters.Add(paramNameFirst);
                    cmd.Parameters.Add(paramNameFirstCanon);
                    cmd.Parameters.Add(paramNameMiddle);
                    cmd.Parameters.Add(paramNameMiddleCanon);
                    cmd.Parameters.Add(paramNameLast);
                    cmd.Parameters.Add(paramNameLastCanon);
                    cmd.Parameters.Add(paramEmail);
                    cmd.Parameters.Add(paramWebsite);
                    cmd.Parameters.Add(paramDescr);

                    SqlTransaction transaction = null;
                    try
                    {
                        //conn.Open();
                        transaction = conn.BeginTransaction();
                        cmd.Transaction = transaction;
                        rowsAffected = cmd.ExecuteNonQuery();
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                    finally
                    {
                        try
                        {
                            conn.Close();
                        }
                        catch { }
                    }
                    /*
                    SqlCommand myCommand = new SqlCommand("SELECT * FROM table WHERE Column = @Param2", myConnection);
                    myCommand.Parameters.Add(myParam2);
                    */

                    //conn.tran
                }
            }


            //conn.Open();  

            return rowsAffected;
        }




        public int Insert()
        {
            int rowsAffected = 0;

            string strConn = (string)ConfigurationManager.ConnectionStrings["POINTS_PROD"].ToString();

            using (SqlConnection conn = new SqlConnection(strConn))
            {
                conn.Open();

                //SqlParameter paramPk = new SqlParameter("@param1", SqlDbType.NVarChar);
                SqlParameter paramAddDt = new SqlParameter("@paramAddDt", SqlDbType.DateTime2);
                SqlParameter paramModDt = new SqlParameter("@paramModDt", SqlDbType.DateTime2);
                SqlParameter paramObjName = new SqlParameter("@paramObjName", SqlDbType.NVarChar);
                SqlParameter paramMarkedDeleteYN = new SqlParameter("@paramMarkedDeleteYN", SqlDbType.NVarChar);
                SqlParameter paramRecId = new SqlParameter("@paramRecId", SqlDbType.Int);
                SqlParameter paramFkLocale = new SqlParameter("@paramFkLocale", SqlDbType.Int);
                SqlParameter paramFkCustomerTypeId = new SqlParameter("@paramFkCustomerTypeId", SqlDbType.Int);
                SqlParameter paramFkCustomerStatusTypeId = new SqlParameter("@paramFkCustomerStatusTypeId", SqlDbType.Int);
                SqlParameter paramUserId = new SqlParameter("@paramUserId", SqlDbType.NVarChar);
                SqlParameter paramPassword = new SqlParameter("@paramPassword", SqlDbType.NVarChar);
                SqlParameter paramSalutation = new SqlParameter("@paramSalutation", SqlDbType.NVarChar);
                SqlParameter paramNameFirst = new SqlParameter("@paramNameFirst", SqlDbType.NVarChar);
                SqlParameter paramNameFirstCanon = new SqlParameter("@paramNameFirstCanon", SqlDbType.NVarChar);
                SqlParameter paramNameMiddle = new SqlParameter("@paramNameMiddle", SqlDbType.NVarChar);
                SqlParameter paramNameMiddleCanon = new SqlParameter("@paramNameMiddleCanon", SqlDbType.NVarChar);
                SqlParameter paramNameLast = new SqlParameter("@paramNameLast", SqlDbType.NVarChar);
                SqlParameter paramNameLastCanon = new SqlParameter("@paramNameLastCanon", SqlDbType.NVarChar);
                SqlParameter paramEmail = new SqlParameter("@paramEmail", SqlDbType.NVarChar);
                SqlParameter paramWebsite = new SqlParameter("@paramWebsite", SqlDbType.NVarChar);
                SqlParameter paramDescr = new SqlParameter("@paramDescr", SqlDbType.NVarChar);
                SqlParameter paramPoints = new SqlParameter("@paramPoints", SqlDbType.Int);
                SqlParameter paramDollars = new SqlParameter("@paramDollars", SqlDbType.Int);

                paramAddDt.Value = this.Add_Dt;
                paramModDt.Value = this.Mod_Dt;
                paramObjName.Value = this.Obj_Name;
                paramMarkedDeleteYN.Value = this.Marked_Delete_Y_N;
                paramRecId.Value = this.Rec_Id;
                paramFkLocale.Value = this.Fk_Locale;
                paramFkCustomerTypeId.Value=this.Fk_Customer_Type_Id;
                paramFkCustomerStatusTypeId.Value = this.Fk_Customer_Status_Type_Id;
                paramUserId.Value = this.Userid;
                paramPassword.Value = this.Password;
                paramSalutation.Value = this.Salutation;
                paramNameFirst.Value = this.Name_First;
                paramNameFirstCanon.Value = this.Name_First_Canon;
                paramNameMiddle.Value = this.Name_Middle;
                paramNameMiddleCanon.Value = this.Name_Middle_Canon;
                paramNameLast.Value = this.Name_Last;
                paramNameLastCanon.Value = this.Name_Last_Canon;
                paramEmail.Value = this.Email;
                paramWebsite.Value = this.Web;
                paramDescr.Value = this.Descr;
                paramPoints.Value = this.ValPoint;
                paramDollars.Value = this.ValDollar;

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    string sqlString =
                   " INSERT INTO CUSTOMER" +
                   " (" +
                   //" pk," +
                    " obj_name," +
                    " add_dt," +
                    " mod_dt," +
                    " marked_delete_y_n," +
                    " rec_id," +
                    " fk_locale," +
                    " fk_customer_type_id," +
                    " fk_customer_status_type_id," +
                    " userid," +
                    " password," +
                    " salutation," +
                    " name_first," +
                    " name_first_canon," +
                    " name_middle," +
                    " name_middle_canon," +
                    " name_last," +
                    " name_last_canon," +
                    " email," +
                    " web," +
                    " descr," +
                    " val_point," +
                    " val_dollar" +
                   " )" +
                   " VALUES" +
                   " (" +
                       " @paramObjName," +
                       " @paramAddDt," +
                       " @paramModDt," +
                       " @paramMarkedDeleteYN," +
                       " @paramRecId," +
                       " @paramFkLocale," +
                       " @paramFkCustomerTypeId," +
                       " @paramFkCustomerStatusTypeId," +
                       " @paramUserId," +
                       " @paramPassword," +
                       " @paramSalutation," +
                       " @paramNameFirst," +
                       " @paramNameFirstCanon," +
                       " @paramNameMiddle," +
                       " @paramNameMiddleCanon," +
                       " @paramNameLast," +
                       " @paramNameLastCanon," +
                       " @paramEmail," +
                       " @paramWebsite," +
                       " @paramDescr," +
                       " @paramPoints," +
                       " @paramDollars" +
                   " )";

                    cmd.CommandText = sqlString;

                    cmd.Parameters.Add(paramAddDt);
                    cmd.Parameters.Add(paramModDt);
                    cmd.Parameters.Add(paramObjName);
                    cmd.Parameters.Add(paramMarkedDeleteYN);
                    cmd.Parameters.Add(paramRecId);
                    cmd.Parameters.Add(paramFkLocale);
                    cmd.Parameters.Add(paramFkCustomerTypeId);
                    cmd.Parameters.Add(paramFkCustomerStatusTypeId);
                    cmd.Parameters.Add(paramUserId);
                    cmd.Parameters.Add(paramPassword);
                    cmd.Parameters.Add(paramSalutation);
                    cmd.Parameters.Add(paramNameFirst);
                    cmd.Parameters.Add(paramNameFirstCanon);
                    cmd.Parameters.Add(paramNameMiddle);
                    cmd.Parameters.Add(paramNameMiddleCanon);
                    cmd.Parameters.Add(paramNameLast);
                    cmd.Parameters.Add(paramNameLastCanon);
                    cmd.Parameters.Add(paramEmail);
                    cmd.Parameters.Add(paramWebsite);
                    cmd.Parameters.Add(paramDescr);
                    cmd.Parameters.Add(paramPoints);
                    cmd.Parameters.Add(paramDollars);

                    SqlTransaction transaction = null;
                    try
                    {
                        //conn.Open();
                        transaction = conn.BeginTransaction();
                        cmd.Transaction = transaction;
                        rowsAffected = cmd.ExecuteNonQuery();
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                    finally
                    {
                        try
                        {
                            conn.Close();
                        }
                        catch { }
                    }
                    /*
                    SqlCommand myCommand = new SqlCommand("SELECT * FROM table WHERE Column = @Param2", myConnection);
                    myCommand.Parameters.Add(myParam2);
                    */

                    //conn.tran
                }
            }


            //conn.Open();  

            return rowsAffected;
        }

    }
}